import initSqlJs from 'sql.js';

async function initSqliteDatabase() {
  const SQL = await initSqlJs({
    locateFile: file => `https://sql.js.org/dist/${file}`
  });

  // 创建数据库实例
  const db = new SQL.Database();
  // 创建表
  db.run(`
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        age INTEGER NOT NULL
    )
  `);

  // 增
  const insertStatement = (name, age) => {
    const stmt = db.prepare("INSERT INTO users (name, age) VALUES (?,?)", [name, age]);
    const result = executeStatement(stmt);
    return result.lastInsertRowid;
  };
  // 删
  const deleteStatement = id => {
    const stmt = db.prepare("DELETE FROM users WHERE id = ?", [id]);
    const result = executeStatement(stmt);
    return result.changes;
  };
  // 改
  const updateStatement = (id, name, age) => {
    const stmt = db.prepare("UPDATE users SET name = COALESCE(?, name), age = COALESCE(?, age) WHERE id = ?", [name, age, id]);
    const result = executeStatement(stmt);
    return result.changes;
  };
  // 查
  const selectStatement = (name, age) => {
    let sql = "SELECT * FROM users WHERE 1=1";
    const params = [];
    if (name) {
      sql += " AND name LIKE ?";
      params.push(`%${name}%`);
    }
    age = parseInt(age);
    if (age > 0 && !isNaN(age)) {
      sql += " AND age = ?";
      params.push(age);
    }
    sql += " LIMIT 10";
    const stmt = db.prepare(sql, params);
    const results = [];
    while (stmt.step()) {
      results.push(stmt.getAsObject());
    }
    stmt.free();
    return results;
  };
  const countStatement = () => {
    const stmt = db.prepare("SELECT COUNT(*) as count FROM users");
    const result = executeStatement(stmt);
    return result.count;
  };
  // 执行
  const executeStatement = stmt => {
    try {
      if (!stmt) return false;
      stmt.step();
      return stmt.getAsObject();
    } catch (error) {
      console.error(error);
      stmt && stmt.free();
      return false;
    } finally {
      stmt && stmt.free() && stmt.reset();
    }
  };

  return {
    db,
    insertStatement,
    deleteStatement,
    updateStatement,
    selectStatement,
    countStatement,
    executeStatement,
    close: () => db.close()
  }
}

export { initSqliteDatabase };